    package davidlauzon.activerecord;
    
    import java.sql.SQLException;
    import java.util.HashMap;
    
    import davidlauzon.activerecord.connection.ConnectionAdapter;
import davidlauzon.activerecord.manager.DeleteManager;
import davidlauzon.activerecord.manager.InsertManager;
import davidlauzon.activerecord.manager.UpdateManager;
    import davidlauzon.activerecord.models.DB;
    import davidlauzon.activerecord.models.Person;
    import davidlauzon.activerecord.nodes.InsertStatement;
    import davidlauzon.activerecord.nodes.field.Field;
import davidlauzon.activerecord.visitor.SqlSerializer;
    
    
    /**
     * java -cp bin:lib/junit-4.7.jar org.junit.runner.JUnitCore newcommerce.db.test.SqlStatementTest
     */
    public class StatementManagerTest extends AbstractTestCase
    {
        /*********************************************************************************************
         * VARIABLES
         *********************************************************************************************/
        private ConnectionAdapter _connection;
        private SqlSerializer     _sqlSerializer;
        private InsertManager     _insertManager;
        private UpdateManager     _updateManager;
        private DeleteManager     _deleteManager;
        
        private InsertStatement _insertStatement;
        private InsertStatement _insertStatementWithFields;
        
        private String _expectedInsertSql;
        
        private Object[]                _insertValues;
        private Object[]                _insertValuesWithFields;
        private HashMap<Field, Object>  _insertValuesHash;
        private Field[]                 _insertColumns; 
        
        private String _actualSql;
        private long   _insertedId;
        private long   _numAffectedRows;
        
        
        
        /*********************************************************************************************
         * TEST SETUP
         *********************************************************************************************/
        protected void setUp() {
            _sqlSerializer = DB.getInstance().getSqlSerializer();
            _connection    = DB.getInstance().getConnection();
            
            _insertValues = new Object[] {1, "David", 3.15f, 28};
            
            _insertStatement = new InsertStatement();
            _insertStatement.setInto(DB.PEOPLE_TABLE);
            _insertStatement.setValues(_insertValues);
            
            _insertColumns          = DB.PEOPLE_TABLE.getFieldsWithoutPK();
            _insertValuesWithFields = new Object[] {"David", 3.15f, 28};
            
            _insertStatementWithFields = new InsertStatement();
            _insertStatementWithFields.setInto(DB.PEOPLE_TABLE);
            _insertStatementWithFields.setColumns(_insertColumns);
            _insertStatementWithFields.setValues(_insertValuesWithFields);
            _expectedInsertSql = "INSERT INTO \"people\" (\"name\",\"size\",\"age\") VALUES ('David',3.15,28)";
        }
        
        
        
        /*********************************************************************************************
         * TESTS
         *********************************************************************************************/
        
        public void testInsertStatement() {
            _actualSql   = _sqlSerializer.visit(_insertStatementWithFields);
            log("testInsertStatement", _actualSql);
            assertEquals(_expectedInsertSql, _actualSql);        
        }
        
    
        public void testInsertManager() throws SQLException {
            // Clear all data first
            _connection.executeUpdate( Constants.DROP_PEOPLE_TABLE );
            _connection.executeUpdate( Constants.CREATE_PEOPLE_TABLE );
            
            // INSERT : into + values
            _insertManager = new InsertManager();
            _actualSql     = _insertManager.into(DB.PEOPLE_TABLE).values(_insertValuesWithFields).toSql();
            log("testInsertManager", _actualSql);
            assertEquals(_expectedInsertSql, _actualSql);
            try {
                _insertedId = _insertManager.into(DB.PEOPLE_TABLE).values(_insertValuesWithFields).insert();
                assertEquals("Inserted id should be first record", 1, _insertedId);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            
            
            // INSERT : columns + into + values 
            _insertManager = new InsertManager();
            _actualSql     = _insertManager.columns(_insertColumns).into(DB.PEOPLE_TABLE).
                                    values(_insertValuesWithFields).toSql();
            log("testInsertManager", _actualSql);
            assertEquals(_expectedInsertSql, _actualSql);
            try {
                _insertedId = _insertManager.columns(_insertColumns).into(DB.PEOPLE_TABLE).
                                    values(_insertValuesWithFields).insert();
                assertEquals("Inserted id should be second record.", 2, _insertedId);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            
            
            // INSERT : into + values (HASH) 
            _insertValuesHash = new HashMap<Field, Object>();
            _insertValuesHash.put(Person.NAME, "David");
            _insertValuesHash.put(Person.AGE, 28);
            _insertValuesHash.put(Person.SIZE, 3.15f);
            
            _insertManager = new InsertManager();
            _actualSql     = _insertManager.into(DB.PEOPLE_TABLE).values(_insertValuesHash).toSql();
            log("testInsertManager", _actualSql);
            try {
                _insertedId = _insertManager.into(DB.PEOPLE_TABLE).values(_insertValuesHash).insert();
                assertEquals("Inserted id should be third record.", 3, _insertedId);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    
    
        public void testUpdateManager() throws SQLException {
            HashMap<Field, Object> updateValuesHash;
            
            // UPDATE : table + values
            updateValuesHash = new HashMap<Field, Object>();
            updateValuesHash.put(Person.NAME, "David");
            updateValuesHash.put(Person.AGE, 38);
            updateValuesHash.put(Person.SIZE, 4.15f);
            
            _updateManager = new UpdateManager();
            _actualSql     = _updateManager.table(DB.PEOPLE_TABLE).set(updateValuesHash).toSql();
            try {
                _numAffectedRows = _updateManager.table(DB.PEOPLE_TABLE).set(updateValuesHash).execute();
                logSql(_actualSql, _numAffectedRows);
                printRecords();
                assertEquals("should have affected all rows", 3, _numAffectedRows);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            
            // UPDATE : table + values + where
            updateValuesHash = new HashMap<Field, Object>();
            updateValuesHash.put(Person.NAME, "Manon");
            updateValuesHash.put(Person.AGE, 25);
            updateValuesHash.put(Person.SIZE, 3.96f);
            
            _updateManager = new UpdateManager();
            _actualSql     = _updateManager.table(DB.PEOPLE_TABLE).set(updateValuesHash).
                                where(" id > ? AND name LIKE '?'")._(2)._("David").toSql();
            try {
                _numAffectedRows = _updateManager.table(DB.PEOPLE_TABLE).set(updateValuesHash).
                        where("id > ? AND name LIKE '?'")._(2)._("David").execute();
                logSql(_actualSql, _numAffectedRows);
                printRecords();
                assertEquals("should have affected only one rows", 1, _numAffectedRows);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    
    
        public void testDeleteManager() throws SQLException {
            // DELETE : WITH WHERE 
            _deleteManager = new DeleteManager();
            _actualSql     = _deleteManager.from(DB.PEOPLE_TABLE).where("size < ?")._(4).toSql();
            try {
                _numAffectedRows = _deleteManager.from(DB.PEOPLE_TABLE).where("size < ?")._(4).execute();
                logSql(_actualSql, _numAffectedRows);
                printRecords();
                assertEquals("should have affected one rows", 1, _numAffectedRows);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            
            // DELETE : all
            _deleteManager = new DeleteManager();
            _actualSql     = _deleteManager.from(DB.PEOPLE_TABLE).toSql();
            try {
                _numAffectedRows = _deleteManager.from(DB.PEOPLE_TABLE).execute();
                logSql(_actualSql, _numAffectedRows);
                printRecords();
                //assertEquals("should have affected all rows", 2, _numAffectedRows);
                // NOTE: statement above returns 0 (Sqlite does not return the
                // the number of affected rows when deleting everything ???
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        
        /*********************************************************************************************
         * INTERNAL METHODS
         *********************************************************************************************/
        @Override
        protected ConnectionAdapter getAdapter() {
            return DB.getInstance().getConnection();
        }
    
    }
